﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using School.Entities;
using School.Common;
using School.Accounting;
using School.Accounting.Reports;

namespace School.Accounting
{
    public static class Voucher
    {
        private static SchoolEntities db = new SchoolEntities();

        public static long AddVoucherFromPaymentOrder(PaymentOrderObject payment, decimal paid, DateTime date)
        {
            long student_id = 0;

            if (payment.NoStudent)
                student_id = GetStudentId(payment.Number);
            else
                student_id = payment.Student_Id;

            ACC_Voucher voucher = new ACC_Voucher();

            voucher.Number = /*Structure.GetVoucherNumber();*/GetVoucherNumber(payment.Expense_Id);
            voucher.Student_Id = student_id;
            voucher.Expense_Id = payment.Expense_Id;
            voucher.Payment_Order_Number = payment.Number;

            if (paid == 0)
                voucher.Value = payment.Value;
            else
                voucher.Value = paid;

            voucher.Date = date;
            voucher.Is_Active = true;
            voucher.Current_Educational_Year = Structure.GetCurrentEductionalYear().First().Id;
            db.AddToACC_Voucher(voucher);
            db.SaveChanges();
            db.AddToACC_Voucher_Payment_Order(new ACC_Voucher_Payment_Order() { Voucher_Number = voucher.Number, Payment_Order_Number = payment.Number });
            db.SaveChanges();

            var ds = from x in db.ACC_Payment_Order_Discounts where x.Payment_Order_Number == payment.Number select x;

            if (ds.Count() > 0)
                foreach (ACC_Payment_Order_Discounts d in ds)
                {
                    ApplyDiscounts(new ACC_Voucher_Discount() { Voucher_Id = voucher.Number, Discount_Id = d.Discount_Id, Discount_Type = d.Type, Discount_Value = d.Value });
                }

            db.SaveChanges();


            return voucher.Number;
        }

        public static void ApplyDiscounts(ACC_Voucher_Discount vd)
        {
            db.AddToACC_Voucher_Discount(vd);

        }

        public static ACC_Voucher GetVoucher(long number)
        {
            var v = from x in db.ACC_Voucher where x.Number == number && x.Is_Active == true select x;

            if (v.Count() > 0)
                return v.First();
            else
                return null;
        }

        public static List<PaymentOrderObject> GetPaymentOrdersByStudent(long student)
        {
            List<PaymentOrderObject> rows = new List<PaymentOrderObject>();

            var py = from x in db.ACC_Payment_Order where x.Student_Id == student select x;

            if (py.Count() > 0)
            {
                foreach (var x in py)
                {
                    rows.Add(new PaymentOrderObject() { Number = x.Payment_Order_Number, NoStudent = false, Expense_Name = x.ACC_Expense_Payments.ACC_Expense.Name, Student_Id = x.Student_Id, Student_Number = long.Parse(x.TE_Student.RegistrationNumber.ToString()), Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var bookexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "400" select x.ACC_Expense.Name).First();

            var pybook = from x in db.SRV_BOOK_Payment_Order where x.Student_Id == student select x;

            if (pybook.Count() > 0)
            {
                foreach (var x in pybook)
                {
                    rows.Add(new PaymentOrderObject() { Number = x.Payment_Order_Number, NoStudent = false, Expense_Name = bookexpense, Student_Id = x.Student_Id, Student_Number = long.Parse(x.TE_Student.RegistrationNumber.ToString()), Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var clothexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "300" select x.ACC_Expense.Name).First();

            var pycloth = from x in db.SRV_CLOTHES_PAYMENT_ORDER where x.Student_Id == student select x;

            if (pycloth.Count() > 0)
            {
                foreach (var x in pycloth)
                {
                    rows.Add(new PaymentOrderObject() { Number = x.Payment_Order_Number, NoStudent = false, Expense_Name = clothexpense, Student_Id = x.Student_Id, Student_Number = long.Parse(x.TE_Student.RegistrationNumber.ToString()), Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var busexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "200" select x.ACC_Expense.Name).First();

            var pybus = from x in db.SRV_Bus_Payment_Order where x.Student_Id == student select x;

            if (pybus.Count() > 0)
            {
                foreach (var x in pybus)
                {
                    rows.Add(new PaymentOrderObject() { Number = x.Payment_order_num, NoStudent = false, Expense_Name = busexpense, Student_Id = x.Student_Id, Student_Number = long.Parse(x.TE_Student.RegistrationNumber.ToString()), Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            return rows;
        }

        public static List<PaymentOrderObject> GetPaymentOrdersByNumber(long number)
        {
            List<PaymentOrderObject> rows = new List<PaymentOrderObject>();

            var py = from x in db.ACC_Payment_Order where x.Payment_Order_Number == number select x;

            if (py.Count() > 0)
            {
                foreach (var x in py)
                {
                    rows.Add(new PaymentOrderObject() { Expense_Id = x.ACC_Expense_Payments.Expense_Id, Number = number, NoStudent = false, Expense_Name = x.ACC_Expense_Payments.ACC_Expense.Name, Student_Id = x.Student_Id, Student_Number = !string.IsNullOrEmpty(x.TE_Student.RegistrationNumber.ToString()) ? long.Parse(x.TE_Student.RegistrationNumber.ToString()) : 0, Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var bookexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "400" select x.ACC_Expense).First();

            var pybook = from x in db.SRV_BOOK_Payment_Order where x.Payment_Order_Number == number select x;

            if (pybook.Count() > 0)
            {
                foreach (var x in pybook)
                {
                    rows.Add(new PaymentOrderObject() { Expense_Id = bookexpense.ID, Number = number, NoStudent = false, Expense_Name = bookexpense.Name, Student_Id = x.Student_Id, Student_Number = !string.IsNullOrEmpty(x.TE_Student.RegistrationNumber.ToString()) ? long.Parse(x.TE_Student.RegistrationNumber.ToString()) : 0, Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var clothexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "300" select x.ACC_Expense).First();

            var pycloth = from x in db.SRV_CLOTHES_PAYMENT_ORDER where x.Payment_Order_Number == number select x;

            if (pycloth.Count() > 0)
            {
                foreach (var x in pycloth)
                {
                    rows.Add(new PaymentOrderObject() { Expense_Id = clothexpense.ID, Number = number, NoStudent = false, Expense_Name = clothexpense.Name, Student_Id = x.Student_Id, Student_Number = !string.IsNullOrEmpty(x.TE_Student.RegistrationNumber.ToString()) ? long.Parse(x.TE_Student.RegistrationNumber.ToString()) : 0, Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var busexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "200" select x.ACC_Expense).First();

            var pybus = from x in db.SRV_Bus_Payment_Order where x.Payment_order_num == number select x;

            if (pybus.Count() > 0)
            {
                foreach (var x in pybus)
                {
                    rows.Add(new PaymentOrderObject() { Expense_Id = busexpense.ID, Number = number, NoStudent = false, Expense_Name = busexpense.Name, Student_Id = x.Student_Id, Student_Number = !string.IsNullOrEmpty(x.TE_Student.RegistrationNumber.ToString()) ? long.Parse(x.TE_Student.RegistrationNumber.ToString()) : 0, Date = x.Date, Value = x.Value, Student_Name = x.TE_Student.Ar_FirstName + " " + x.TE_Student.Ar_SecondName + " " + x.TE_Student.Ar_ThirdNmae + " " + x.TE_Student.Ar_FourthName });
                }
            }

            var filexpense = (from x in db.ACC_Expenses_Screens where x.SEC_Screen.Code == "100" select x.ACC_Expense).First();

            var pyfile = from x in db.ADM_File_Order where x.Payment_Order_Number == number select x;

            if (pyfile.Count() > 0)
            {
                foreach (var x in pyfile)
                {
                    rows.Add(new PaymentOrderObject() { Expense_Id = filexpense.ID, Number = number, NoStudent = true, Student_Number = 0, Expense_Name = filexpense.Name, Student_Id = 0, Date = x.Date, Value = x.Price, Student_Name = x.FirstName + " " + x.SecondName + " " + x.ThirdName + " " + x.FourthName });
                }
            }

            return rows;
        }

        public static VoucherReportObject GetVoucherReport(long number)
        {
            var v = db.ACC_Voucher.Single(x => x.Number == number);

            VoucherReportObject voucher = new VoucherReportObject()
            {
                Voucher_Number = v.Number,
                Expense_Name = v.ACC_Expense.Name,
                Student_Id = v.Student_Id,
                Student_Name = v.TE_Student.Ar_FirstName + " " + v.TE_Student.Ar_SecondName + " " + v.TE_Student.Ar_ThirdNmae + " " + v.TE_Student.Ar_FourthName,
                Campus_Name = v.TE_Student.ORG_Campus.Name,
                Certitificate_Name = v.TE_Student.ORG_Certificate.Name,
                Stage_Name = v.TE_Student.ORG_Stage.Name,
                Grade_Name = v.TE_Student.ORG_Grade.Name,
                Value = v.Value,
                Date = v.Date
            };

            return voucher;
        }

        public static IEnumerable<VoucherDiscountsObject> GetDiscounts(long number)
        {
            List<VoucherDiscountsObject> rows = new List<VoucherDiscountsObject>();

            var ds = from x in db.ACC_Voucher_Discount where x.Voucher_Id == number select x;

            foreach (var d in ds)
            {
                rows.Add(new VoucherDiscountsObject() { Code = d.ACC_Discount.Code, Name = d.ACC_Discount.Name, Type = d.Discount_Type, Value = d.Discount_Value });
            }

            return rows;
        }

        public static long GetStudentId(long PaymentOrderNumber)
        {
            long Student_Id = 0;

            var payment = db.ADM_File_Order.Single(x => x.Payment_Order_Number == PaymentOrderNumber);

            String fileNumber;

            fileNumber = String.Format("{0:00}", payment.ORG_Campus.Code);
            fileNumber += String.Format("{0:00}", payment.ORG_Certificate.Code);
            DateTime dt1 = payment.Date;

            fileNumber += String.Format("{0:ddMMyy}", dt1);

            long id = 0;
            var ps = from x in db.TE_Student select x.Id;

            if (ps.Count() > 0)
                id = ps.Max();

            fileNumber += String.Format("{0:0000}", id + 1);

            TE_Student s = new TE_Student();

            s.FileNumber = long.Parse(fileNumber);
            s.Campus_Id = payment.Campus_Id;
            s.Certificate_Id = payment.Certificate_Id;
            s.Stage_Id = payment.Stage_Id;
            s.Grade_ID = payment.Grade_Id;
            s.Ar_FirstName = payment.FirstName;
            s.Ar_SecondName = payment.SecondName;
            s.Ar_ThirdNmae = payment.ThirdName;
            s.Ar_FourthName = payment.FourthName;
            s.Status_Id = 1;

            db.AddToTE_Student(s);
            db.SaveChanges();

            Student_Id = s.Id;

            return Student_Id;
        }

        public static IEnumerable<ACC_Voucher> Search(DateTime FromDate, DateTime ToDate, List<long> expenses = null)
        {
            var v = from x in db.ACC_Voucher where x.Date >= FromDate && x.Date <= ToDate select x;

            if (expenses.Count() > 0)
            {
                v = v.Where(x => expenses.Contains(x.Expense_Id));
            }

            return v;
        }

        public static void SetFalse(long number)
        {
            var v = (from x in db.ACC_Voucher where x.Number == number select x).First();
            v.Is_Active = false;
            db.SaveChanges();
        }

        public static IEnumerable<ACC_Voucher> SearchVouchers(List<long> expenses = null, DateTime? FromDate = null, DateTime? ToDate = null, long Campus = 0, long Certificate = 0, long Stage = 0, long Grade = 0, long Class = 0)
        {
            long CurrentYear = Structure.GetCurrentEductionalYear().First().Id;

            var v = from x in db.ACC_Voucher where x.Current_Educational_Year == CurrentYear && x.Is_Active == true select x;

            if (FromDate != null)
                v = v.Where(x => x.Date >= FromDate);

            if (ToDate != null)
                v = v.Where(x => x.Date <= ToDate);

            if (Campus != 0)
                v = v.Where(x => x.TE_Student.Campus_Id == Campus);

            if (Certificate != 0)
                v = v.Where(x => x.TE_Student.Certificate_Id == Certificate);

            if (Stage != 0)
                v = v.Where(x => x.TE_Student.Stage_Id == Stage);

            if (Grade != 0)
                v = v.Where(x => x.TE_Student.Grade_ID == Grade);

            if (Class != 0)
                v = v.Where(x => x.TE_Student.TE_Class_Student.Where(y => y.Student_Id == x.Student_Id && y.EductionalYear_Id == CurrentYear).Select(z => z.Class_Id).First() == Class);

            if (expenses.Count() > 0)
            {
                v = v.Where(x => expenses.Contains(x.Expense_Id));
            }

            return v;
        }

        public static IEnumerable<ReturnedReportObject> SearchReturned(List<long> expenses = null, DateTime? FromDate = null, DateTime? ToDate = null, long Campus = 0, long Certificate = 0, long Stage = 0, long Grade = 0, long Class = 0)
        {
            long CurrentYear = Structure.GetCurrentEductionalYear().First().Id;

            var v = from x in db.ACC_Returned_Vouchers where x.ACC_Voucher.Current_Educational_Year == CurrentYear select x;

            if (FromDate != null)
                v = v.Where(x => x.Date >= FromDate);

            if (ToDate != null)
                v = v.Where(x => x.Date <= ToDate);

            if (Campus != 0)
                v = v.Where(x => x.ACC_Voucher.TE_Student.Campus_Id == Campus);

            if (Certificate != 0)
                v = v.Where(x => x.ACC_Voucher.TE_Student.Certificate_Id == Certificate);

            if (Stage != 0)
                v = v.Where(x => x.ACC_Voucher.TE_Student.Stage_Id == Stage);

            if (Grade != 0)
                v = v.Where(x => x.ACC_Voucher.TE_Student.Grade_ID == Grade);

            if (Class != 0)
                v = v.Where(x => x.ACC_Voucher.TE_Student.TE_Class_Student.Where(y => y.Student_Id == x.ACC_Voucher.Student_Id && y.EductionalYear_Id == CurrentYear).Select(z => z.Class_Id).First() == Class);

            if (expenses.Count() > 0)
            {
                v = v.Where(x => expenses.Contains(x.ACC_Voucher.Expense_Id));
            }

            List<ReturnedReportObject> rows = new List<ReturnedReportObject>();

            foreach (var x in v)
            {
                rows.Add(new ReturnedReportObject()
                {
                    Expense = x.ACC_Voucher.ACC_Expense.Name,
                    StudentName = x.ACC_Voucher.TE_Student.Ar_FirstName + " " + x.ACC_Voucher.TE_Student.Ar_SecondName + " " + x.ACC_Voucher.TE_Student.Ar_ThirdNmae + " " + x.ACC_Voucher.TE_Student.Ar_FourthName,
                    StudentNumber = x.ACC_Voucher.TE_Student.RegistrationNumber,
                    VoucherDate = x.ACC_Voucher.Date,
                    VoucherNumber = x.ACC_Voucher.Number,
                    VoucherValue = x.ACC_Voucher.Value,
                    ReturnedDate = x.Date,
                    ReturnedNumber = x.Id,
                    ReturnedValue = x.Value
                });
            }

            return rows;
        }

        public static long ReturnVoucher(long number, decimal paid)
        {
            SetFalse(number);
            var rv = new ACC_Returned_Vouchers() { Voucher_Number = number, Date = DateTime.Now, Value = paid };

            db.AddToACC_Returned_Vouchers(rv);
            db.SaveChanges();

            return rv.Id;
        }

        public static IEnumerable<ACC_Voucher> GetVouchersByStudent(long student)
        {
            long currentYear = Structure.GetCurrentEductionalYear().First().Id;

            return from x in db.ACC_Voucher where x.Student_Id == student && x.Current_Educational_Year == currentYear && x.Is_Active == true select x;
        }

        public static IEnumerable<ACC_Returned_Vouchers> GetReturnsByStudent(long student)
        {
            long currentYear = Structure.GetCurrentEductionalYear().First().Id;

            return from x in db.ACC_Returned_Vouchers where x.ACC_Voucher.Current_Educational_Year == currentYear && x.ACC_Voucher.Student_Id == student select x;
        }

        public static IEnumerable<ACC_Voucher> GetVoucherByNumber(long Number)
        {
            return from x in db.ACC_Voucher where x.Number == Number select x;
        }

        public static IEnumerable<ACC_Voucher> GetVoucherByPaymentOrder(long Number)
        {
            return from x in db.ACC_Voucher where x.Payment_Order_Number == Number select x;
        }

        private static long GetVoucherNumber(long expense)
        {
            int g = int.Parse((from x in db.ACC_Expense where x.ID == expense select x.Group_Id).First().ToString());

            var c = (from x in db.ACC_Group where x.Id == g select x).First();

            long t = c.Counter;

            c.Counter++;

            return t;
        }

    }

    public class PaymentOrderObject
    {
        public long Number { get; set; }
        public long Expense_Id { get; set; }
        public string Expense_Name { get; set; }
        public long Student_Id { get; set; }
        public long Student_Number { get; set; }
        public string Student_Name { get; set; }
        public decimal Value { get; set; }
        public DateTime? Date { get; set; }
        public bool NoStudent { get; set; }
    }
}
